<?php

require_once('Vega/Dao/Table.php');

class Mobitv_Admin_ReportDao extends Vega_Dao_Table {

    public function isSubCp($cpId, $parentId=0) {
        if ($parentId)
            $sql = "SELECT * FROM admin WHERE id=$cpId AND parent_id=$parentId";
        else
            $sql = "SELECT * FROM admin WHERE id=$cpId AND parent_id>0";

        $this->_db->query($sql);
        $row = $this->_db->fetchRow();

        return $row ? true : false;
    }

    public function isAddon($channelName) {
        $channelName = $this->_db->valueQuote($channelName);
        $sql = "SELECT * FROM channel WHERE name=$channelName AND (type=" . ADDON_CHANNEL . " OR type=" . EVENT_CHANNEL . ")";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();

        return $row ? TRUE : FALSE;
    }

    public function getDetail($reportId) {
        $reportId = intval($reportId);
        $sql = "SELECT * FROM report WHERE id=$reportId";
        $this->_db->query($sql);

        return $this->_db->fetchRow();
    }

    public function getParentCategoryList() {
        $sql = "SELECT * FROM clip_category WHERE parent_id=0 AND visible=0 ORDER BY `order`";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        return $rows;
    }

    public function getCategoryNameFromId($catId) {
        $catId = intval($catId);
        $sql = "SELECT name FROM clip_category WHERE id=$catId";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();

        return $row ? $row['name'] : false;
    }

    public function getCategoryDataFromId($catId) {
        $catId = intval($catId);
        $sql = "SELECT * FROM clip_category WHERE id=$catId";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();

        return $row ? $row : false;
    }

    public function getAddonChannelname() {
        $sql = "SELECT name FROM channel WHERE type=" . ADDON_CHANNEL . " ORDER BY name";
        $this->_db->query($sql);

        $rows = array();
        while ($row = $this->_db->fetchRow()) {
            $rows[] = $row['name'];
        }

        return $rows;
    }

    public function getBasicChannelname() {
        $sql = "SELECT name FROM channel WHERE type=" . BASIC_CHANNEL . " ORDER BY name";
        $this->_db->query($sql);

        $rows = array();
        while ($row = $this->_db->fetchRow()) {
            $rows[] = $row['name'];
        }

        return $rows;
    }

    public function getChannelname() {
        $sql = "SELECT name FROM channel ORDER BY name";
        $this->_db->query($sql);

        $rows = array();
        while ($row = $this->_db->fetchRow()) {
            $rows[] = $row['name'];
        }

        return $rows;
    }

    public function getListByQuery($reportId) {
        $reportId = intval($reportId);
        $sql = "SELECT query FROM report WHERE id=$reportId";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        if ($row) {
            $queries = $row['query'];
            if ($queries) {
                $queries = explode(';', $queries);
                foreach ($queries AS $sql) {
                    $sql = trim($sql);
                    if ($sql) {
                        try {
                            $this->_db->query($sql);
                        } catch (Exception $e) {
                            $_SESSION['msg'] = "Cú pháp lỗi, hãy xem lại!";
                        }
                    }
                }
                return $this->_db->fetchRowset();
            }
            else
                return false;
        }

        return false;
    }

    public function getList() {
        $sql = "SELECT * FROM report ORDER BY name";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function add($data) {
        $reportName = $this->_db->valueQuote($data['name']);
        $reportQuery = $this->_db->valueQuote($data['query']);

        $sql = "INSERT INTO report SET name=$reportName, query=$reportQuery";
        $this->_db->queryWrite($sql);
    }

    public function save($reportId, $data) {
        $reportId = intval($reportId);
        $reportName = $this->_db->valueQuote($data['name']);
        $reportQuery = $this->_db->valueQuote($data['query']);

        $sql = "UPDATE report SET name=$reportName, query=$reportQuery WHERE id=$reportId";
        $this->_db->queryWrite($sql);
    }

    public function getUpdatedTime() {
        $sql = "SELECT MAX(`updated_time`) AS date FROM report_daily";
        $date = NULL;
        $result = $this->_db->query($sql);
        if ($row = $this->_db->fetchRow($result))
            $date = $row['date'];

        return $date;
    }

    public function getGeneralStatistic($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT * FROM report_daily WHERE cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function getRevenuaSubscribeVodPackage($fromDate, $toDate, $cpId, $action = 'vod_package', $method = 'wap') {
        $sql = "SELECT COUNT(msisdn) msisdn, SUM(charge) charge, content ";
        $sql .= "FROM `transaction` WHERE created_time>='$fromDate' AND created_time<='$toDate 23:59:59' AND action='$action' AND method = '$method' ";
        if($cpId){
            $sql .= " AND content in (SELECT id FROM clip_category WHERE cp_id = $cpId) ";
        }
        $sql .= " GROUP BY content ";
        //echo $sql;
        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function getAllUserInVodPackageSubscribe($fromDate, $toDate, $content, $action = 'vod_package', $method = 'wap') {
        $sql = "SELECT t.* ";
        $sql .= " FROM `transaction` t
                WHERE t.created_time>='$fromDate' AND t.created_time<='$toDate 23:59:59' AND t.action='$action' AND t.method = '$method' AND t.content = '$content' ";
        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function getVodStatistic($cpId, $fromDate, $toDate) {
        $cpId = intval($cpId);
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, watch_count, download_count FROM report_daily WHERE cp_id=$cpId AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function getVideoStatistic($videoId, $fromDate, $toDate) {
        $cpId = intval($cpId);
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, watch_count, download_count, present_count FROM report_daily_video WHERE video_id=$videoId AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        $rows = $this->_db->fetchRowset();
        $data = array();

        if ($rows) {
            foreach ($rows AS $row) {
                $data[$row['date']] = $row;
            }
        }

        return $data;
    }

    public function getVideoStatistic2($videoId, $fromDate) {
        $cpId = intval($cpId);
        $startDate = $fromDate;
        $fromDate = $this->_db->valueQuote($fromDate);
        $sql = "SELECT DATE(started_time) AS date, COUNT(*) AS count FROM watching_list WHERE clip_id=$videoId AND started_time>=$fromDate AND action='watch' GROUP BY date";
        $this->_db->query($sql);
        $wRows = $this->_db->fetchRowset();

        $sql = "SELECT DATE(started_time) AS date, COUNT(*) AS count FROM watching_list WHERE clip_id=$videoId AND started_time>=$fromDate AND action='download' GROUP BY date";
        $this->_db->query($sql);
        $dRows = $this->_db->fetchRowset();

        $w = $d = array();
        foreach ($wRows AS $item)
            $w[$item['date']] = $item['count'];
        foreach ($dRows AS $item)
            $d[$item['date']] = $item['count'];

        $data = array();
        $toDate = date('Y-m-d');

        for ($day = strtotime($startDate); $day <= strtotime($toDate); $day+=60 * 60 * 24) {
            $date = date('Y-m-d', $day);
            $data[$date]['watch_count'] = isset($w[$date]) ? intval($w[$date]) : 0;
            $data[$date]['download_count'] = isset($d[$date]) ? intval($d[$date]) : 0;
        }

        return $data;
    }

    /*
     * Lấy thống kê thuê bao đăng ký
     */

    public function getSubscriptionStatistic($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, tv_subscription_count FROM report_daily WHERE cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê chuyển thuê bao
     */

    public function getSwitchPackageStatistic($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, tv_switch_subscription_count FROM report_daily WHERE cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê thuê bao hủy đăng ký
     */

    public function getUnsubscriptionStatistic($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, tv_unsubscription_count FROM report_daily WHERE cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê thời lượng xem LiveTV
     */

    public function getLivetvDurationStatistic($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, tv_channel_duration FROM report_daily WHERE cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê số lần đăng ký kênh addon
     */

    public function getLivetvAddonChannelSubscription($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, tv_addon_channel_subscription_count FROM report_daily WHERE cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê số lần hủy đăng ký kênh addon
     */

    public function getLivetvAddonChannelUnsubscription($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, tv_addon_channel_unsubscription_count FROM report_daily WHERE cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê doanh thu theo CP
     */

    public function getVodRevenueStatistic($cpId, $fromDate, $toDate) {
        $cpId = intval($cpId);
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $filter_by_cp = $cpId ? " AND a.id=$cpId" : "";
        $sql = "SELECT date, vod_watch_count, vod_download_count, vod_revenue, cp_id, a.name, a.fullname FROM report_daily rd INNER JOIN admin a ON rd.cp_id=a.id WHERE date>=$fromDate AND date<=$toDate $filter_by_cp";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê doanh thu theo chuyên mục
     */

    public function getVodCategoryStatistic($cpId, $fromDate, $toDate) {
        $cpId = intval($cpId);
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, vod_category_statistic, cp_id FROM report_daily rd WHERE rd.cp_id=$cpId AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê doanh thu theo chuyên mục cac VOD free
     */

    public function getVodCategoryStatisticFree($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, vod_category_statistic_free FROM report_daily rd WHERE rd.cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê những thành viên xem VOD nhiều
     */

    public function getTopUserVod($fromDate, $toDate) {
        $cpId = intval($cpId);
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, vod_most_watched_user, vod_most_downloaded_user WHERE date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lấy thống kê những thành viên xem VOD nhiều
     */

    public function getTopUserLivetv($fromDate, $toDate) {
        $cpId = intval($cpId);
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, vod_most_watched_user, vod_most_downloaded_user WHERE date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function getVodwatchStatistic($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, unsubscribe_count FROM report_daily WHERE cp_id=0 AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function getVideoNameFromIdList($idList) {
        if (!$idList)
            return null;

        $list = "(" . implode(",", $idList) . ")";
        $sql = "SELECT id,title FROM clip WHERE id IN $list";
        $this->_db->query($sql);

        $nameList = array();
        while ($row = $this->_db->fetchRow()) {
            $nameList[$row['id']] = $row['title'];
        }

        return $nameList;
    }

    public function getTopList($cpId, $fromDate, $toDate) {
        $cpId = intval($cpId);
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT most_watched_video, most_downloaded_video, most_watched_user, most_downloaded_user FROM report_daily WHERE cp_id=$cpId AND date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function getTopVod($type, $fromDate, $toDate, $limit) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate . ' 23:59:59');
        switch ($type) {
            case 'w':
                $_action_filter = "action='watch'";
                break;
            case 'd':
                $_action_filter = "action='download'";
                break;
            case 'a':
            default:
                $_action_filter = "1=1";
        }

        $limit = intval($limit);

        //$sql = "SELECT clip_id, count(*) as `count`, sum(charge) AS 'revenue' FROM watching_list wl WHERE $_action_filter  AND date(started_time)>=$fromDate AND date(started_time)<=$toDate GROUP BY clip_id ORDER BY `count` DESC LIMIT $limit";
        $sql = "SELECT clip_id, count(clip_id) as `count`, sum(charge) AS 'revenue' FROM watching_list wl WHERE $_action_filter  AND started_time >= $fromDate AND started_time <= $toDate GROUP BY clip_id ORDER BY `count` DESC LIMIT $limit";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        return $this->getVideoListFromIds($rows);
    }

    private function getVideoListFromIds($rows) {
        if (!$rows)
            return null;

        foreach ($rows AS $row) {
            $idArray[] = $row['clip_id'];
            $ccArray[] = $row['count'];
            $reArray[] = $row['revenue'];
        }
        $IDString = "(" . implode(",", $idArray) . ")";

        $sql = "SELECT c.*, cc.name AS category_name, a.name as username, b.name as subcpname FROM clip c LEFT JOIN admin a ON c.member_id = a.id LEFT JOIN admin b ON c.subcp_id = b.id LEFT JOIN clip_category cc ON c.category_id=cc.id WHERE c.id IN $IDString";
        $result = $this->_db->query($sql);
        while ($row = $this->_db->fetchRow($result)) {
            foreach ($idArray AS $i => $item) {
                if ($item == $row['id']) {
                    $row['count'] = $ccArray[$i];
                    $row['revenue'] = $reArray[$i];
                    $list[$i] = $row;
                }
            }
        }

        //re-order
        ksort($list);

        //return
        return $list;
    }

    /*
     * Lay danh sach cac CP
     */

    public function getCpList($cp) {
        $filterByCp = $cp ? " AND a.parent_id=$cp" : "";
        $sql = "SELECT a.id, a.name, a.fullname, a.parent_id FROM admin a INNER JOIN admin_group ag ON a.group_id=ag.id WHERE ag.abbr='cp'$filterByCp ORDER BY a.fullname";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    /*
     * Lay danh sach ID cac CP
     */

    public function getCpIdList() {
        $sql = "SELECT a.id FROM admin a WHERE a.abbr='cp'";
        $this->_db->query($sql);
        $cpList = array('0');
        while ($row = $this->_db->fetchRow()) {
            $cpList[] = $row['id'];
        }

        return $cpList;
    }

    /*
     * Lay tat ca video cua CP
     */

    public function getVideoListFromCpList($cpList) {
        $videoList = array();
        foreach ($cpList AS $cpId) {
            $cpId = intval($cpId);
            $sql = "SELECT id FROM clip WHERE member_id=$cpId OR subcp_id=$cpId";
            $this->_db->query($sql);
            $videoList[$cpId] = array();

            while ($row = $this->_db->fetchRow()) {
                $videoList[$cpId][] = $row['id'];
            }
        }

        return $videoList;
    }

    /*
     * Thong ke chung kenh addon
     */

    public function getLivetvAddonChannelStatistic($fromDate, $toDate) {
        
    }

    /*
     * Thong ke so thue bao duoc kich hoat
     */

    public function updateActiveUser($date) {
        $date = $this->_db->valueQuote($date);

        // Addvanced users
        $this->_db->query("SELECT count(*) AS `count` FROM user WHERE is_subscribed=" . ADVANCED_PACKAGE);
        $row = $this->_db->fetchRow();
        $advancedUserCount = $row ? $row['count'] : 0;

        // Basic users
        $this->_db->query("SELECT count(*) AS `count` FROM user WHERE is_subscribed=" . BASIC_PACKAGE);
        $row = $this->_db->fetchRow();
        $basicUserCount = $row ? $row['count'] : 0;

        $activeUser = array('advanced' => $advancedUserCount, 'basic' => $basicUserCount, 'all' => $advancedUserCount + $basicUserCount);
        $activeUser = $this->_db->valueQuote(json_encode($activeUser));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    active_user=$activeUser
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    active_user=$activeUser";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke so thue bao xem VOD theo CP
     */

    public function updateDailyVodSubscriberWatchCount($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_cp_sql = $isSubCp ? "subcp_id=$cpId" : "member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? "AND content IN (SELECT id FROM clip WHERE $filter_cp_sql)" : "";
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_subscriber_watch_count=(SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_watch' $filter_by_cp)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_subscriber_watch_count=(SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_watch' $filter_by_cp)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke so thue bao tai VOD theo CP
     */

    public function updateDailyVodSubscriberDownloadCount($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_cp_sql = $isSubCp ? "subcp_id=$cpId" : "member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? "AND content IN (SELECT id FROM clip WHERE $filter_cp_sql)" : "";
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_subscriber_download_count=(SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_download' $filter_by_cp)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_subscriber_download_count=(SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_download' $filter_by_cp)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke so thue bao tang VOD theo CP
     */

    public function updateDailyVodSubscriberPresentCount($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_cp_sql = $isSubCp ? "subcp_id=$cpId" : "member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? "AND content IN (SELECT id FROM clip WHERE $filter_cp_sql)" : "";
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_subscriber_present_count=(SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_present' $filter_by_cp)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_subscriber_present_count=(SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_present' $filter_by_cp)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke luot xem VOD theo CP
     */

    public function updateDailyVodWatchCount($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $filter_cp_sql = $isSubCp ? "AND subcp_id=$cpId" : "AND member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? $filter_cp_sql : "";

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content=c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_watch' AND t.charge>0 AND c.source='vn' $filter_by_cp";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $vnWatchCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content=c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_watch' AND t.charge>0 AND c.source='nn' $filter_by_cp";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $nnWatchCount = $row['count'];

        $data = array('vn' => $vnWatchCount, 'nn' => $nnWatchCount, 'all' => $vnWatchCount + $nnWatchCount);
        $data = $this->_db->valueQuote(json_encode($data));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_watch_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_watch_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Calculate the download count day by day
     */

    public function updateDailyVodDownloadCount($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $filter_cp_sql = $isSubCp ? "AND subcp_id=$cpId" : "AND member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? $filter_cp_sql : "";

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content=c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_download' AND t.charge>0 AND c.source='vn' $filter_by_cp";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $vnDownloadCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content=c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_download' AND t.charge>0 AND c.source='nn' $filter_by_cp";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $nnDownloadCount = $row['count'];

        $data = array('vn' => $vnDownloadCount, 'nn' => $nnDownloadCount, 'all' => $vnDownloadCount + $nnDownloadCount);
        $data = $this->_db->valueQuote(json_encode($data));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_download_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_download_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Calculate the download count day by day
     */

    public function updateDailyVodPresentCount($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $filter_cp_sql = $isSubCp ? "AND subcp_id=$cpId" : "AND member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? $filter_cp_sql : "";

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content=c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_present' AND t.charge>0 AND c.source='vn' $filter_by_cp";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $vnPresentCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content=c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_present' AND t.charge>0 AND c.source='nn' $filter_by_cp";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $nnPresentCount = $row['count'];

        $data = array('vn' => $vnPresentCount, 'nn' => $nnPresentCount, 'all' => $vnPresentCount + $nnPresentCount);
        $data = $this->_db->valueQuote(json_encode($data));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_present_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_present_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu xem VOD theo CP
     */

    public function updateDailyVodWatchRevenue($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_cp_sql = $isSubCp ? "subcp_id=$cpId" : "member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? "AND content IN (SELECT id FROM clip WHERE $filter_cp_sql)" : "";

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_watch_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_watch' $filter_by_cp),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_watch_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_watch' $filter_by_cp),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu tải VOD theo CP
     */

    public function updateDailyVodDownloadRevenue($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_cp_sql = $isSubCp ? "subcp_id=$cpId" : "member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? "AND content IN (SELECT id FROM clip WHERE $filter_cp_sql)" : "";

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_download_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_download' $filter_by_cp),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_download_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_download' $filter_by_cp),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu tặng VOD theo CP
     */

    public function updateDailyVodPresentRevenue($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_cp_sql = $isSubCp ? "subcp_id=$cpId" : "member_id=$cpId AND subcp_id=0";
        $filter_by_cp = $cpId ? "AND content IN (SELECT id FROM clip WHERE $filter_cp_sql)" : "";

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_present_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_present' $filter_by_cp),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_present_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_present' $filter_by_cp),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu VOD chung theo CP
     */

    public function updateDailyVodRevenue($date, $cpId) {
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_revenue=vod_watch_revenue+vod_download_revenue+vod_present_revenue
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_revenue=vod_watch_revenue+vod_download_revenue+vod_present_revenue";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu đăng ký mới LiveTV
     */

    public function updateDailySubscriptionRevenue($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_subscription_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action IN ('subscribe', 'switch')),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_subscription_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action IN ('subscribe', 'switch')),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê số lần thêm giờ LiveTV
     */

    public function updateDailyLivetvAddtimeCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_addtime_count=IFNULL((SELECT sum(content) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='addTime'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_addtime_count=IFNULL((SELECT sum(content) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='addTime'),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu thêm giờ LiveTV
     */

    public function updateDailyLivetvAddtimeRevenue($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_addtime_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='addTime'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_addtime_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='addTime'),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu thêm kênh addon của LiveTV
     */

    public function updateDailyLivetvAddonRevenue($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_addon_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='addAddonChannel'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_addon_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='addAddonChannel'),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu trừ cước tháng
     */

    public function updateDailyLivetvMonfeeRevenue($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_monfee_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action IN ('monfee', 'monfee_addon')),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_monfee_revenue=IFNULL((SELECT sum(charge) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action IN ('monfee', 'monfee_addon')),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thong ke doanh thu LiveTV
     */

    public function updateDailyLivetvRevenue($date) {
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_revenue=tv_addtime_revenue+tv_addon_revenue+tv_subscription_revenue+tv_monfee_revenue
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_revenue=tv_addtime_revenue+tv_addon_revenue+tv_subscription_revenue+tv_monfee_revenue";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê danh sách top video được xem trong ngày theo CP
     */

    public function updateDailyVodMostWatchedVideo($date, $cpId, $mostWatchedVideoList) {
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $mostWatchedVideoList = $this->_db->valueQuote(json_encode($mostWatchedVideoList));
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_most_watched_video=$mostWatchedVideoList
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_most_watched_video=$mostWatchedVideoList";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê danh sách top video được tải trong ngày theo CP
     */

    public function updateDailyVodMostDownloadedVideo($date, $cpId, $mostDownloadVideoList) {
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $mostDownloadVideoList = $this->_db->valueQuote(json_encode($mostDownloadVideoList));
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_most_downloaded_video=$mostDownloadVideoList
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_most_downloaded_video=$mostDownloadVideoList";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê danh sách top thuê bao xem VOD trong ngày theo CP
     */

    public function updateDailyVodMostWatchedUser($date, $cpId, $mostWatchedUserList) {
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $mostWatchedUserList = $this->_db->valueQuote(json_encode($mostWatchedUserList));
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_most_watched_user=$mostWatchedUserList
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_most_watched_user=$mostWatchedUserList";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê danh sách top thuê bao tải VOD trong ngày theo CP
     */

    public function updateDailyVodMostDownloadedUser($date, $cpId, $mostDownloadedUserList) {
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $mostDownloadedUserList = $this->_db->valueQuote(json_encode($mostDownloadedUserList));
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_most_downloaded_user=$mostDownloadedUserList
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_most_downloaded_user=$mostDownloadedUserList";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê theo chuyên mục VOD
     */

    function updateDailyVodCategoryStatistic($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_cp_sql = $isSubCp ? "c.subcp_id=$cpId" : "c.member_id=$cpId AND c.subcp_id=0";
        $filter_by_cp = $cpId ? "AND $filter_cp_sql" : "";
        $rows = $wvRows = $wnRows = $dvRows = $dnRows = $w = $d = array();

        // Lấy thống kê xem VOD VN theo chuyên mục
        $sql = "SELECT c.category_id AS id, count(t.id) AS 'w', sum(charge) AS 'wr' FROM `transaction` t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content = c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_watch' AND charge>0 AND c.source='vn' $filter_by_cp GROUP by c.category_id ORDER BY c.category_id";
        $this->_db->query($sql);
        $wvRows = $this->_db->fetchRowset();

        // Lấy thống kê xem VOD NN theo chuyên mục
        $sql = "SELECT c.category_id AS id, count(t.id) AS 'w', sum(charge) AS 'wr' FROM `transaction` t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content = c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_watch' AND charge>0 AND c.source='nn' $filter_by_cp GROUP by c.category_id ORDER BY c.category_id";
        $this->_db->query($sql);
        $wnRows = $this->_db->fetchRowset();

        // Lấy thống kê tải VOD VN theo chuyên mục
        $sql = "SELECT c.category_id AS id, count(t.id) AS 'd', sum(charge) AS 'dr' FROM `transaction` t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content = c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_download' AND charge>0 AND c.source='vn' $filter_by_cp GROUP by c.category_id ORDER BY c.category_id";
        $this->_db->query($sql);
        $dvRows = $this->_db->fetchRowset();

        // Lấy thống kê tải VOD NN theo chuyên mục
        $sql = "SELECT c.category_id AS id, count(t.id) AS 'd', sum(charge) AS 'dr' FROM `transaction` t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content = c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_download' AND charge>0 AND c.source='nn' $filter_by_cp GROUP by c.category_id ORDER BY c.category_id";
        $this->_db->query($sql);
        $dnRows = $this->_db->fetchRowset();

        // Lấy danh sách các chuyên mục
        $sql = "SELECT id FROM clip_category";
        $this->_db->query($sql);
        $cats = $this->_db->fetchRowset();

        foreach ($wvRows AS $item) {
            $wv[$item['id']] = $item;
        }
        foreach ($wnRows AS $item) {
            $wn[$item['id']] = $item;
        }
        foreach ($dvRows AS $item) {
            $dv[$item['id']] = $item;
        }
        foreach ($dnRows AS $item) {
            $dn[$item['id']] = $item;
        }

        foreach ($cats AS $item) {
            $id = intval($item['id']);
            $rows[$id] = array();
            $rows[$id]['wv'] = isset($wv[$id]['w']) ? intval($wv[$id]['w']) : 0;
            $rows[$id]['wn'] = isset($wn[$id]['w']) ? intval($wn[$id]['w']) : 0;
            $rows[$id]['dv'] = isset($dv[$id]['d']) ? intval($dv[$id]['d']) : 0;
            $rows[$id]['dn'] = isset($dn[$id]['d']) ? intval($dn[$id]['d']) : 0;

            $wvr = isset($wv[$id]['wr']) ? intval($wv[$id]['wr']) : 0;
            $wnr = isset($wn[$id]['wr']) ? intval($wn[$id]['wr']) : 0;
            $dvr = isset($dv[$id]['dr']) ? intval($dv[$id]['dr']) : 0;
            $dnr = isset($dn[$id]['dr']) ? intval($dn[$id]['dr']) : 0;

            $rows[$id]['wr'] = $wvr + $wnr;
            $rows[$id]['dr'] = $dvr + $dnr;
            $rows[$id]['tr'] = $rows[$id]['wr'] + $rows[$id]['dr'];
        }

        $data = $this->_db->valueQuote(json_encode($rows));
        // Lưu vào
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=$cpId,
                    updated_time=now(),
                    vod_category_statistic=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_category_statistic=$data";

        $this->_db->queryWrite($sql);
        echo 'done';
    }

    /*
     * Thống kê theo chuyên mục VOD cac video free
     */

    function updateDailyVodCategoryStatisticFree($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $rows = $wRows = $dRows = $w = $d = array();

        // Lấy thống kê xem VOD theo chuyên mục
        $sql = "SELECT c.category_id AS id, count(t.id) AS 'w', sum(charge) AS 'wr' FROM `transaction` t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content = c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_watch' AND charge=0 GROUP by c.category_id ORDER BY c.category_id";
        $this->_db->query($sql);
        $wRows = $this->_db->fetchRowset();

        // Lấy thống kê xem VOD theo chuyên mục
        $sql = "SELECT c.category_id AS id, count(t.id) AS 'd', sum(charge) AS 'dr' FROM `transaction` t FORCE INDEX (idx_time) INNER JOIN clip c ON t.content = c.id WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_download' AND charge=0 GROUP by c.category_id ORDER BY c.category_id";
        $this->_db->query($sql);
        $dRows = $this->_db->fetchRowset();

        // Lấy danh sách các chuyên mục
        $sql = "SELECT id FROM clip_category";
        $this->_db->query($sql);
        $cats = $this->_db->fetchRowset();

        foreach ($wRows AS $item) {
            $w[$item['id']] = $item;
        }
        foreach ($dRows AS $item) {
            $d[$item['id']] = $item;
        }

        foreach ($cats AS $item) {
            $id = intval($item['id']);
            $rows[$id] = array();
            $rows[$id]['w'] = isset($w[$id]['w']) ? intval($w[$id]['w']) : 0;
            $rows[$id]['d'] = isset($d[$id]['d']) ? intval($d[$id]['d']) : 0;
            $rows[$id]['wr'] = isset($w[$id]['wr']) ? intval($w[$id]['wr']) : 0;
            $rows[$id]['dr'] = isset($d[$id]['dr']) ? intval($d[$id]['dr']) : 0;
            $rows[$id]['tr'] = $rows[$id]['wr'] + $rows[$id]['dr'];
        }

        $data = $this->_db->valueQuote(json_encode($rows));

        // Lưu vào
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_category_statistic_free=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_category_statistic_free=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Lấy danh sách video được xem nhiều nhất trong ngày theo CP
     * Limit: 30
     */

    function getDailyMostWatchedVideoList($date, $cpId, $isSubCp) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_by_cp = $cpId ? "AND clip_id IN (SELECT id FROM clip WHERE member_id=$cpId OR subcp_id=$cpId)" : "";

        // Get top 30 records
        $sql = "SELECT clip_id, count(*) AS watch_count FROM watching_list WHERE started_time>=$date AND started_time<$toDate AND action='watch' AND charge>0 $filter_by_cp GROUP BY clip_id ORDER BY watch_count DESC LIMIT 30";
        $this->_db->query($sql);

        $mostWatchedVideoList = array();
        while ($row = $this->_db->fetchRow()) {
            $mostWatchedVideoList[$row['clip_id']] = intval($row['watch_count']);
        }

        return $mostWatchedVideoList;
    }

    /*
     * Lấy danh sách video được tải nhiều nhất trong ngày theo CP
     * Limit: 30
     */

    function getDailyMostDownloadedVideoList($date, $cpId) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_by_cp = $cpId ? "AND clip_id IN (SELECT id FROM clip WHERE member_id=$cpId OR subcp_id=$cpId)" : "";

        // Get top 30 records
        $sql = "SELECT clip_id, count(*) AS download_count FROM watching_list WHERE started_time>=$date AND started_time<$toDate AND action='download' AND charge>0 $filter_by_cp GROUP BY clip_id ORDER BY download_count DESC LIMIT 30";
        $this->_db->query($sql);

        $mostDownloadedVideoList = array();
        while ($row = $this->_db->fetchRow()) {
            $mostDownloadedVideoList[$row['clip_id']] = intval($row['download_count']);
        }

        return $mostDownloadedVideoList;
    }

    /*
     * Lấy danh sách thuê bao xem nhiều nhất trong ngày theo CP
     * Limit: 30
     */

    function getDailyMostWatchedUserList($date, $cpId) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_by_cp = $cpId ? "AND clip_id IN (SELECT id FROM clip WHERE member_id=$cpId OR subcp_id=$cpId)" : "";

        // Get top 30 records
        $sql = "SELECT user_id, count(*) AS watch_count FROM watching_list WHERE started_time>=$date AND started_time<$toDate AND action='watch' AND charge>0 $filter_by_cp GROUP BY user_id ORDER BY watch_count DESC LIMIT 30";
        $this->_db->query($sql);

        $mostWatchedUserList = array();
        while ($row = $this->_db->fetchRow()) {
            $mostWatchedUserList[$row['user_id']] = intval($row['watch_count']);
        }

        return $mostWatchedUserList;
    }

    /*
     * Lấy danh sách thuê bao tải nhiều nhất trong ngày theo CP
     * Limit: 30
     */

    function getDailyMostDownloadedUserList($date, $cpId) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $cpId = intval($cpId);
        $filter_by_cp = $cpId ? "AND clip_id IN (SELECT id FROM clip WHERE member_id=$cpId OR subcp_id=$cpId)" : "";

        // Get top 30 records
        $sql = "SELECT user_id, count(*) AS download_count FROM watching_list WHERE started_time>=$date AND started_time<$toDate AND action='download' AND charge>0 $filter_by_cp GROUP BY user_id ORDER BY download_count DESC LIMIT 30";
        $this->_db->query($sql);

        $mostDownloadedUserList = array();
        while ($row = $this->_db->fetchRow()) {
            $mostDownloadedUserList[$row['user_id']] = intval($row['download_count']);
        }

        return $mostDownloadedUserList;
    }

    /*
     * Thống kê số lượt đăng ký LiveTV theo phương thức
     */

    function updateDailySubscriptionCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        /*
         * Advanced package
         */

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='subscribe' AND method='wap' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content=" . ADVANCED_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $wapSubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='subscribe' AND method='sms' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content=" . ADVANCED_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $smsSubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='subscribe' AND method='api' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content=" . ADVANCED_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $apiSubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='subscribe' AND method='ws' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content=" . ADVANCED_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $wsSubscription = $row ? $row['tongso'] : 0;

        $advancedPackage = array();
        $advancedPackage['wap'] = $wapSubscription;
        $advancedPackage['sms'] = $smsSubscription;
        $advancedPackage['api'] = $apiSubscription;
        $advancedPackage['ws'] = $wsSubscription;
        $advancedPackage['all'] = $wapSubscription + $smsSubscription + $apiSubscription + $wsSubscription;

        /*
         * Basic package
         */

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='subscribe' AND method='wap' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content=" . BASIC_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $wapSubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='subscribe' AND method='sms' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content=" . BASIC_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $smsSubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='subscribe' AND method='api' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content=" . BASIC_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $apiSubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='subscribe' AND method='ws' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content=" . BASIC_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $wsSubscription = $row ? $row['tongso'] : 0;

        $basicPackage = array();
        $basicPackage['wap'] = $wapSubscription;
        $basicPackage['sms'] = $smsSubscription;
        $basicPackage['api'] = $apiSubscription;
        $basicPackage['ws'] = $wsSubscription;
        $basicPackage['all'] = $wapSubscription + $smsSubscription + $apiSubscription + $wsSubscription;

        /*
         * Monfee success
         */
        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='monfee' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content <> ''";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $monfeeCount = $row ? $row['tongso'] : 0;

        /*
         * Monfee error
         */
        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction_error FORCE INDEX (idx_time) WHERE action='monfee' AND created_time>=$date AND created_time<$toDate AND charge>0 AND content <> ''";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $monfeeErrorCount = $row ? $row['tongso'] : 0;

        $subscription = array('advanced' => $advancedPackage, 'basic' => $basicPackage, 'monfee' => $monfeeCount, 'monfee_error' => $monfeeErrorCount);
        $data = $this->_db->valueQuote(json_encode($subscription));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_subscription_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_subscription_count=$data";

        $this->_db->queryWrite($sql);
    }

    /**
     * Cap nhat lai so lieu tong so TB tru cuoc thang (khong bao gom nhung TB chua tru du tien)
     * @param $date: ngay can update
     */
    public function updateCountMsisdnMonfee($date){
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        
        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='monfee' AND created_time>=$date AND created_time<$toDate AND charge>1000 AND content <> ''";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $monfeeCount = $row ? $row['tongso'] : 0;

        $sql = "SELECT tv_subscription_count FROM report_daily WHERE date=$date";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $tv_subscription_count = $row ? json_decode($row['tv_subscription_count'], true) : '';

        $subscription = array('advanced' => $tv_subscription_count['advanced'], 'basic' => $tv_subscription_count['basic'], 'monfee' => $monfeeCount);
        $data = $this->_db->valueQuote(json_encode($subscription));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_subscription_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_subscription_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê số lượt chuyển gói cước LiveTV theo phương thức
     */

    function updateDailySwitchSubscriptionCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='switch' AND created_time>=$date AND created_time<$toDate AND content=" . BASIC_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $advance2basic = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='switch' AND created_time>=$date AND created_time<$toDate AND content=" . ADVANCED_PACKAGE;
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $basic2advance = $row ? $row['tongso'] : 0;

        $data = array('advance2basic' => $advance2basic, 'basic2advance' => $basic2advance);
        $data = $this->_db->valueQuote(json_encode($data));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_switch_subscription_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_switch_subscription_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê tất cả lượt đăng ký LiveTV (kể cả free)
     */

    function updateDailySubscriptionCountAll($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_subscription_count_all=IFNULL((SELECT count(*) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='subscribe'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_subscription_count_all=IFNULL((SELECT count(*) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='subscribe'),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê số lượt đăng ký LiveTV theo phương thức
     */

    function updateDailyUnsubscriptionCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='unsubscribe' AND method='wap' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $wapUnsubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='unsubscribe' AND method='sms' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $smsUnsubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='unsubscribe' AND method='api' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $apiUnsubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='unsubscribe' AND method='ws' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $wsUnsubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='suspend' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $suspendUnsubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='suspend_2g' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $suspend2GUnsubscription = $row ? $row['tongso'] : 0;

        $sql = "SELECT count(distinct msisdn) AS 'tongso' FROM transaction FORCE INDEX (idx_time) WHERE action='suspend_chown' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $suspendChownUnsubscription = $row ? $row['tongso'] : 0;

        $data = array();
        $data['wap'] = $wapUnsubscription;
        $data['sms'] = $smsUnsubscription;
        $data['api'] = $apiUnsubscription;
        $data['ws'] = $wsUnsubscription;
        $data['suspend'] = $suspendUnsubscription;
        $data['suspend_2g'] = $suspend2GUnsubscription;
        $data['suspend_chown'] = $suspendChownUnsubscription;
        $data['all'] = $wapUnsubscription + $smsUnsubscription + $apiUnsubscription + $wsUnsubscription + $suspendUnsubscription + $suspend2GUnsubscription + $suspendChownUnsubscription;

        $data = $this->_db->valueQuote(json_encode($data));
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_unsubscription_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_unsubscription_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê số lượt xem LiveTV
     */

    function updateDailyLivetvHitCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        // Tính số lần xem bằng iPhone (gần đúng)
        $sql = "SELECT count(*) AS 'count' FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate AND source='http' GROUP BY msisdn, channel";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        $iphoneCount = $rows ? count($rows) : 0;

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_hit_count=$iphoneCount+IFNULL((SELECT count(*) FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate AND source='rtsp'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_hit_count=$iphoneCount+IFNULL((SELECT count(*) FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate AND source='rtsp'),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê số lượt xem LiveTV theo kênh
     */

    function updateDailyLivetvChannelHitCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        // Tính số lần xem Darwin
        $sql = "SELECT channel, count(*) AS 'count' FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate AND source='darwin' GROUP BY channel";
        $this->_db->query($sql);
        $drows = array();
        while ($row = $this->_db->fetchRow()) {
            $drows[$row['channel']] = $row['count'];
        }

        // Tính số lần xem bằng iPhone (gần đúng)
        $sql = "SELECT channel, count(*) AS 'count' FROM (SELECT channel, count(*) AS 'count' FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate AND source='iphone' GROUP BY msisdn, channel) c GROUP BY channel";
        $this->_db->query($sql);
        $irows = array();
        while ($row = $this->_db->fetchRow()) {
            $irows[$row['channel']] = $row['count'];
        }

        foreach ($drows AS $channel => $row) {
            // Hit = darwin + iphone
            $drows[$channel] += isset($irows[$channel]) ? intval($irows[$channel]) : 0;
            $sum += $drows[$channel];
        }

        $drows['ALL'] = $sum;

        $data = $this->_db->valueQuote(json_encode($drows));
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_channel_hit_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_channel_hit_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê số thuê bao xem LiveTV
     */

    function updateDailyLivetvSubscriber($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "SELECT COUNT(DISTINCT msisdn) tongso FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate AND package=1";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $basic = $row ? $row['tongso'] : 0;

        $sql = "SELECT COUNT(DISTINCT msisdn) tongso FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate AND package=2";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $lite = $row ? $row['tongso'] : 0;

        $data = array();
        $data['basic'] = $basic;
        $data['lite'] = $lite;
        $data['all'] = $all = $basic + $lite;
        $data = $this->_db->valueQuote(json_encode($data));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_subscriber_count = IFNULL((SELECT COUNT(DISTINCT msisdn) FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate),0)
                    ,tv_watching_count = $data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_subscriber_count = IFNULL((SELECT COUNT(DISTINCT msisdn) FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate),0)
                    ,tv_watching_count = $data";
        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê tổng thời gian xem LiveTV
     */

    function updateDailyLivetvDuration($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_duration=IFNULL((SELECT sum(play_time) FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_duration=IFNULL((SELECT sum(play_time) FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê thời gian xem trung bình của LiveTV
     */

    function updateDailyLivetvAverageDuration($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    tv_avg_duration=IFNULL(SEC_TO_TIME(ROUND(tv_duration/tv_subscriber_count)),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    tv_avg_duration=IFNULL(SEC_TO_TIME(ROUND(tv_duration/tv_subscriber_count)),0)";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê số lần đăng ký kênh Addon
     */

    function updateDailyAddonChannelSubscription($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        // Lấy danh sách kênh add-on
        $data = array();
        $sum = 0;
        $sql = "SELECT name FROM channel WHERE `type`=" . ADDON_CHANNEL . " OR `type`=" . EVENT_CHANNEL;
        $this->_db->query($sql);
        $rows = $row = $this->_db->fetchRowset();
        foreach ($rows AS $item) {
            $channel = $item['name'];

            // Tính số lần thêm kênh add-on
            $sql = "SELECT count(distinct msisdn) AS 'count' FROM transaction FORCE INDEX (idx_time) WHERE action='addAddonChannel' AND content='$channel' AND charge>0 AND created_time>=$date AND created_time<$toDate";
            $this->_db->query($sql);
            $row1 = $this->_db->fetchRow();

            // Tính số lần thêm kênh khi trừ cước tháng
            $sql = "SELECT count(distinct msisdn) AS 'count' FROM transaction_monfee WHERE action='addon' AND content='$channel' AND created_time>=$date AND created_time<$toDate";
            $this->_db->query($sql);
            $row2 = $this->_db->fetchRow();

            $data[$channel] = $total = $row1['count'] + $row2['count'];
            $sum += $total;
        }

        $data['ALL'] = $sum;

        $data = $this->_db->valueQuote(json_encode($data));
        $sql = "INSERT INTO report_daily
            SET
                date=$date,
                cp_id=0,
                updated_time=now(),
                tv_addon_channel_subscription_count=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                tv_addon_channel_subscription_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê số lần hủy đăng ký kênh Addon
     */

    function updateDailyAddonChannelUnsubscription($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "SELECT content AS 'channel', count(*) AS 'count' FROM transaction FORCE INDEX (idx_time) WHERE action='removeAddonChannel' AND created_time>=$date AND created_time<$toDate GROUP BY content ORDER BY `count` DESC";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        $data = array();
        if ($rows) {
            foreach ($rows AS $item) {
                $data[$item['channel']] = $item['count'];
            }
        }

        $data = $this->_db->valueQuote(json_encode($data));
        $sql = "INSERT INTO report_daily
            SET
                date=$date,
                cp_id=0,
                updated_time=now(),
                tv_addon_channel_unsubscription_count=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                tv_addon_channel_unsubscription_count=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Thống kê thời lượng xem theo kênh
     */

    function updateDailyChannelDuration($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "SELECT channel AS 'channel', sum(play_time) AS 'sum' FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$date AND started_time<$toDate GROUP BY channel ORDER BY `sum` DESC";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        if ($rows) {
            $data = array();
            foreach ($rows AS $item) {
                $data[$item['channel']] = $item['sum'];
            }
        }

        $data = $this->_db->valueQuote(json_encode($data));
        $sql = "INSERT INTO report_daily
            SET
                date=$date,
                cp_id=0,
                updated_time=now(),
                tv_channel_duration=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                tv_channel_duration=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Lấy chuỗi danh sách VOD xem nhiều nhất
     */

    function updateDailyTopVideoList() {
        // Lay danh sach cac chuyen muc cua radio
        $radioCategoryList = $this->getParentCategoryList();
        $list = array(0);
        foreach ($radioCategoryList AS $item)
            $list[] = $item['id'];
        $radioCategories = '(' . implode(',', $list) . ')';

        // Lay danh sach top 50 VOD trong 1 ngay gan day
        $date = date("Y-m-d");
        $startedTime = $this->_db->valueQuote(date("Y-m-d H:i:s", strtotime("-1 day")));
        $sql = "SELECT c.id FROM clip c
                            INNER JOIN (SELECT clip_id FROM watching_list WHERE started_time>=$startedTime AND action='watch' GROUP BY clip_id ORDER BY count(clip_id) DESC LIMIT 50) w on c.id=w.clip_id
                            WHERE c.deleted=0 AND c.active=1 AND c.category_id NOT IN $radioCategories";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        $list = array();
        foreach ($rows AS $row) {
            $list[] = $row['id'];
        }

        // Sap xep lai theo thu tu ngau nhien
        $list = rand_array($list);

        $data = $this->_db->valueQuote(implode(',', $list));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
            SET
                date=$date,
                cp_id=0,
                updated_time=now(),
                vod_top_video_list=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                vod_top_video_list=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Lấy chuỗi danh sách Radio xem nhiều nhất
     */

    function updateDailyTopRadioList() {
        // Lay danh sach cac chuyen muc cua radio
        $radioCategoryList = $this->getParentCategoryList();
        $list = array(0);
        foreach ($radioCategoryList AS $item)
            $list[] = $item['id'];
        $radioCategories = '(' . implode(',', $list) . ')';

        // Lay danh sach top 50 VOD trong 1 ngay gan day
        $date = date("Y-m-d");
        $startedTime = $this->_db->valueQuote(date("Y-m-d H:i:s", strtotime("-1 day")));
        $sql = "SELECT c.id FROM watching_list w INNER JOIN clip c ON w.clip_id=c.id WHERE w.started_time>=$startedTime AND w.action='watch' AND c.deleted=0 AND c.active=1 AND c.category_id IN $radioCategories GROUP BY c.id ORDER BY count(*) DESC LIMIT 50";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        $list = array();
        foreach ($rows AS $row) {
            $list[] = $row['id'];
        }
        $data = $this->_db->valueQuote(implode(',', $list));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
            SET
                date=$date,
                cp_id=0,
                updated_time=now(),
                vod_top_radio_list=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                vod_top_radio_list=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Lấy chuỗi danh sách VOD xem nhiều nhất theo chuyên mục
     */

    function updateDailyTopVideoByCategory() {
        // Lay danh sach chuyen muc
        $sql = "SELECT id, parent_id FROM clip_category";
        $this->_db->query($sql);
        $cats = $this->_db->fetchRowset();
        $catList = array();
        foreach ($cats AS $cat) {
            $catId = $cat['id'];
            $catParent = $cat['parent_id'];
            $catList[$catId] = $catId;
            if ($catParent == 0) {
                foreach ($cats AS $row) {
                    if ($row['parent_id'] == $catId)
                        $catList[$catId] .= "," . $row['id'];
                }
            }
        }

        $fromTime = date("Y-m-d H:i:s", strtotime("-1 week", time()));
        $fromTime = $this->_db->valueQuote($fromTime);

        foreach ($catList AS $catId => $arr) {
            $sql = "SELECT c.id FROM clip c INNER JOIN watching_list wc FORCE INDEX (idx_started_time) ON c.id=wc.clip_id WHERE wc.started_time>=$fromTime AND wc.action='watch' AND c.deleted=0 AND c.active=1 AND c.category_id IN ($arr) GROUP BY c.id ORDER BY count(*) DESC LIMIT 50";
            $this->_db->query($sql);
            $rows = $this->_db->fetchRowset();

            $list = array();
            foreach ($rows AS $row) {
                $list[] = $row['id'];
            }
            $data = $this->_db->valueQuote(implode(',', $list));

            $sql = "INSERT INTO report_video_category
                    SET
                        category_id=$catId,
                        updated_time=now(),
                        most_viewed_list=$data
                    ON DUPLICATE KEY UPDATE
                        updated_time=now(),
                        most_viewed_list=$data";

            $this->_db->queryWrite($sql);
        }
    }

    /*
     * Lấy chuỗi danh sách VOD thời sự xem nhiều nhất
     */

    function updateDailyTopNewsList() {
        // Lay danh sach top 50 VOD thời sự trong 1 ngay gan day
        $newsCategoryId = 39; // ID chuyen muc con cua thoi su
        $date = date("Y-m-d");
        $startedTime = $this->_db->valueQuote(date("Y-m-d H:i:s", strtotime("-1 day")));
        $sql = "select c.id from clip c inner join (SELECT clip_id FROM watching_list WHERE started_time>=$startedTime AND action='watch' GROUP BY clip_id ORDER BY count(*) DESC LIMIT 50) w on c.id=w.clip_id WHERE c.deleted=0 AND c.active=1 AND (category_id=1 OR category_id=$newsCategoryId)";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        $list = array();
        foreach ($rows AS $row) {
            $list[] = $row['id'];
        }
        $data = $this->_db->valueQuote(implode(',', $list));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
            SET
                date=$date,
                cp_id=0,
                updated_time=now(),
                vod_top_news_list=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                vod_top_news_list=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * Lấy chuỗi danh sách kênh xem nhiều nhất
     */

    function updateDailyTopChannelList($date) {
        // Lay danh sach top trong 3 ngay gan day
        $startedTime = $this->_db->valueQuote(date("Y-m-d H:i:s", strtotime("-3 days", strtotime($date))));
        $sql = "SELECT c.id FROM channel c INNER JOIN (SELECT channel FROM livetv_watching_list FORCE INDEX (idx_time) WHERE started_time>=$startedTime AND started_time<now() GROUP BY channel ORDER BY count(*) DESC LIMIT 5) w ON c.name=w.channel";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();

        $list = array();
        foreach ($rows AS $row) {
            $list[] = $row['id'];
        }
        $data = $this->_db->valueQuote(implode(',', $list));
        $date = $this->_db->valueQuote($date);

        $sql = "INSERT INTO report_daily
            SET
                date=$date,
                cp_id=0,
                updated_time=now(),
                tv_top_channel_list=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                tv_top_channel_list=$data";

        $this->_db->queryWrite($sql);
    }

    /*
     * VIDEO REPORT
     */

    function updateDailyVideo($date) {
        // Lay danh sach VOD trong ngay
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        // Danh sach xem VOD
        $watchCount = array();
        $sql = "SELECT content, count(*) AS `count` FROM transaction WHERE action='vod_watch' AND charge>0 AND created_time>=$date AND created_time<$toDate GROUP BY content";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();
        foreach ($rows AS $row)
            $watchCount[$row['content']] = $row['count'];

        // Danh sach tai VOD
        $downloadCount = array();
        $sql = "SELECT content, count(*) AS `count` FROM transaction WHERE action='vod_download' AND charge>0 AND created_time>=$date AND created_time<$toDate GROUP BY content";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();
        foreach ($rows AS $row)
            $downloadCount[$row['content']] = $row['count'];

        // Danh sach tang VOD
        $presentCount = array();
        $sql = "SELECT content, count(*) AS `count` FROM transaction WHERE action='vod_present' AND charge>0 AND created_time>=$date AND created_time<$toDate GROUP BY content";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();
        foreach ($rows AS $row) {
            $content = explode(",", $row['content']);
            $videoId = isset($content[0]) ? $content[0] : 0;
            $presentCount[$videoId] = $row['count'];
        }

        $videoIdList = array_merge_recursive(array_keys($watchCount), array_keys($downloadCount), array_keys($presentCount));

        // Thong ke
        foreach ($videoIdList AS $id) {
            if ($id) {
                $w = isset($watchCount[$id]) ? $watchCount[$id] : 0;
                $d = isset($downloadCount[$id]) ? $downloadCount[$id] : 0;
                $p = isset($presentCount[$id]) ? $presentCount[$id] : 0;

                $sql = "INSERT INTO report_daily_video
                        SET
                            date=$date,
                            video_id=$id,
                            watch_count=$w,
                            download_count=$d,
                            present_count=$p
                        ON DUPLICATE KEY UPDATE
                            watch_count=$w,
                            download_count=$d,
                            present_count=$p";

                $this->_db->queryWrite($sql);
            }
        }

        // Free memory
        unset($watchCount);
        unset($downloadCount);
        unset($presentCount);
        unset($rows);
    }

    /*
     * MONITOR STATISTIC
     */

    function getMonitorStatus($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate);
        $sql = "SELECT date, vcgw, hlr, updated_time FROM report_daily_monitor WHERE date>=$fromDate AND date<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    function updateDailyVcgwStatus($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $data = array();

        $sql = "SELECT status, count(*) AS 'tongso' FROM transaction_error WHERE created_time>=$date AND created_time<$toDate GROUP BY status";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();
        $total = 0;

        if ($rows) {
            foreach ($rows AS $row) {
                $data[$row['status']] = $row['tongso'];
                $total += $data[$row['status']];
            }

            $data['all'] = $total;
        }

        $data = $this->_db->valueQuote(json_encode($data));

        $sql = "INSERT INTO report_daily_monitor
            SET
                date=$date,
                updated_time=now(),
                vcgw=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                vcgw=$data";

        $this->_db->queryWrite($sql);
    }

    function updateDailyHlrStatus($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $data = array();

        $sql = "SELECT hlr_status, count(*) AS 'tongso' FROM session WHERE created_time>=$date AND created_time<$toDate GROUP BY hlr_status
                UNION
                SELECT hlr_status, count(*) AS 'tongso' FROM session_storage WHERE created_time>=$date AND created_time<$toDate GROUP BY hlr_status";
        $this->_db->query($sql);
        $rows = $this->_db->fetchRowset();
        $total = 0;

        if ($rows) {
            foreach ($rows AS $row) {
                $data[$row['hlr_status']] = $row['tongso'];
                $total += $data[$row['hlr_status']];
            }

            $data['all'] = $total;
        }

        $data = $this->_db->valueQuote(json_encode($data));

        $sql = "INSERT INTO report_daily_monitor
            SET
                date=$date,
                updated_time=now(),
                hlr=$data
            ON DUPLICATE KEY UPDATE
                updated_time=now(),
                hlr=$data";

        $this->_db->queryWrite($sql);
    }

    /**
     * Tong so thue bao dang ky su dung chuyen muc video
     * @param <type> $date
     */
    public function updateDailyVodPackageSubscribedCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_package_subscribed_count=(SELECT count(distinct content) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_package')
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_package_subscribed_count=(SELECT count(distinct content) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_package')";
        $this->_db->queryWrite($sql);
    }

    /**
     * Tong doanh thu dang ky su dung chuyen muc video
     * @param <type> $date
     */
    public function updateDailyVodPackageSubscribedRevenue($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_package_subscribed_revenue=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_package'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_package_subscribed_revenue=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action='vod_package'),0)";

        $this->_db->queryWrite($sql);
    }

    /**
     * Tong so thue bao dang ky su dung dich vu ban tin video hot hang ngay
     * @param <type> $date
     */
    public function updateDailyVodHotSubscribedCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_hot_subscribed_count=(SELECT count(distinct user_id) FROM clip_hot_registration WHERE deleted =0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_hot_subscribed_count=(SELECT count(distinct user_id) FROM clip_hot_registration WHERE deleted =0)";

        $this->_db->queryWrite($sql);
    }

    /**
     * Tong so thue bao huy dich vu ban tin video hot hang ngay
     * @param <type> $date
     */
    public function updateDailyVodHotUnSubscribedCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_hot_unsubscribed_count=(SELECT count(distinct user_id) FROM clip_hot_registration WHERE deleted =1)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_hot_unsubscribed_count=(SELECT count(distinct user_id) FROM clip_hot_registration WHERE deleted =1)";

        $this->_db->queryWrite($sql);
    }

    /**
     * Doanh thu ban tin video hot hang ngay
     * @param <type> $date
     */
    public function updateDailyVodHotRevenue($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_hot_revenue=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action IN('sendVodHot','subscribeVodHot')),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_hot_revenue=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action IN('sendVodHot','subscribeVodHot')),0)";
        $this->_db->queryWrite($sql);
    }

    public function updateWeeklyVodHotSubscribe($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_weekly_subscribed=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'subscribeVodHotWeekly'),0)
                    ,vod_daily_subscribed=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'subscribeVodHotDaily'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_weekly_subscribed=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'subscribeVodHotWeekly'),0)
                    ,vod_daily_subscribed=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'subscribeVodHotDaily'),0)";
        $this->_db->queryWrite($sql);
    }

    public function updateWeeklyVodHotUnSubscribe($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_weekly_unsubscribed=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND content = '1' AND action = 'unsubscribeVodHot'),0)
                    ,vod_daily_unsubscribed=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND content = '0' AND action = 'unsubscribeVodHot'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_weekly_unsubscribed=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND content = '1' AND action = 'unsubscribeVodHot'),0)
                    ,vod_daily_unsubscribed=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND content = '0' AND action = 'unsubscribeVodHot'),0)";
        $this->_db->queryWrite($sql);
    }

    public function updateWeeklyVodHotRevenueCount($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_weekly_count=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'sendVodWeekly' AND charge>0),0)
                    ,vod_daily_count=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'sendVodDaily' AND charge>0),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_weekly_count=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'sendVodWeekly' AND charge>0),0)
                    ,vod_daily_count=IFNULL((SELECT count(distinct msisdn) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'sendVodDaily' AND charge>0),0)";
        $this->_db->queryWrite($sql);
    }

    public function updateWeeklyVodHotRevenueSubscribed($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_weekly_revenue_subscribed=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'subscribeVodHotWeekly'),0)
                    ,vod_daily_revenue_subscribed=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'subscribeVodHotDaily'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_weekly_revenue_subscribed=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'subscribeVodHotWeekly'),0)
                    ,vod_daily_revenue_subscribed=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'subscribeVodHotDaily'),0)";
        $this->_db->queryWrite($sql);
    }

    public function updateWeeklyVodHotRevenue($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_weekly_charged=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'sendVodWeekly'),0)
                    ,vod_daily_charged=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'sendVodDaily'),0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_weekly_charged=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'sendVodWeekly'),0)
                    ,vod_daily_charged=IFNULL((SELECT sum(charge) FROM transaction FORCE INDEX (idx_time) WHERE created_time>=$date AND created_time<$toDate AND action = 'sendVodDaily'),0)";
        $this->_db->queryWrite($sql);
    }

    public function updateWeeklyVodHotTotal($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);
        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_weekly_total= (SELECT count(user_id) FROM clip_hot_registration WHERE deleted = 0 AND daily = 1)
                    ,vod_daily_total= (SELECT count(user_id) FROM clip_hot_registration WHERE deleted = 0 AND daily = 0)
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_weekly_total= (SELECT count(user_id) FROM clip_hot_registration WHERE deleted = 0 AND daily = 1)
                    ,vod_daily_total= (SELECT count(user_id) FROM clip_hot_registration WHERE deleted = 0 AND daily = 0)";
        $this->_db->queryWrite($sql);
    }

    /**
     * Toanhv
     * @param $date: Ngay thong ke
     * Thong ke dich vu VOD tren LIVETV
     */
    public function updateVodChannelOnLiveTV($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        /**
         * Kenh Phim ONLINE
         */
        $channel = DKP;
        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='subscribeVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelDKCount = $row['count'];

        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='subscribeVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $moneyDKCount = $row['count'];

        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_livetv_watch' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $moneyWatchingCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='unsubscribevodlivetv' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelHUYCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='extensionVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelCronCount = $row['count'];
        
        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='extensionVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelCronChargeCount = $row['count'];

        $sql = "SELECT count(*) as `count` from catagory_registration WHERE catagory_id=$channel AND deleted=0";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $totalActived = $row['count'];

        $arr_phimonline = array('total'=>$totalActived, 'subscribe'=>$channelDKCount, 'revenueSubscribe'=>$moneyDKCount, 'revenueWatching'=>$moneyWatchingCount, 'revenueCron'=>$channelCronChargeCount, 'cronCount'=>$channelCronCount, 'unsubscribe'=>$channelHUYCount);

        /*
         * Kenh hai Domino
         */
        $channel = DKD;
        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='subscribeVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelDKCount = $row['count'];

        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='subscribeVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $moneyDKCount = $row['count'];

        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_livetv_watch' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $moneyWatchingCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='unsubscribevodlivetv' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelHUYCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='extensionVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelCronCount = $row['count'];

        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='extensionVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelCronChargeCount = $row['count'];

        $sql = "SELECT count(*) as `count` from catagory_registration WHERE catagory_id=$channel AND deleted=0";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $totalActived = $row['count'];

        $arr_domino = array('total'=>$totalActived, 'subscribe'=>$channelDKCount, 'revenueSubscribe'=>$moneyDKCount, 'revenueWatching'=>$moneyWatchingCount, 'revenueCron'=>$channelCronChargeCount, 'cronCount'=>$channelCronCount, 'unsubscribe'=>$channelHUYCount);

        /*
         * Kenh tin tuc Onnews
         */
        $channel = DKO;
        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='subscribeVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelDKCount = $row['count'];

        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='subscribeVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $moneyDKCount = $row['count'];

        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='vod_livetv_watch' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $moneyWatchingCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='unsubscribevodlivetv' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelHUYCount = $row['count'];

        $sql = "SELECT count(*) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='extensionVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelCronCount = $row['count'];

        $sql = "SELECT sum(charge) AS `count` FROM transaction t FORCE INDEX (idx_time) WHERE t.created_time>=$date AND t.created_time<$toDate AND t.action='extensionVodLiveTV' AND t.content='$channel'";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $channelCronChargeCount = $row['count'];

        $sql = "SELECT count(*) as `count` from catagory_registration WHERE catagory_id=$channel AND deleted=0";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $totalActived = $row['count'];

        $arr_onnews = array('total'=>$totalActived, 'subscribe'=>$channelDKCount, 'revenueSubscribe'=>$moneyDKCount, 'revenueWatching'=>$moneyWatchingCount, 'revenueCron'=>$channelCronChargeCount, 'cronCount'=>$channelCronCount, 'unsubscribe'=>$channelHUYCount);

        /*
         * Update to db
         */
        $sumTotal =  $arr_phimonline['revenueSubscribe'] + $arr_phimonline['revenueWatching'] + $arr_phimonline['revenueCron'];
        $sumTotal +=  $arr_domino['revenueSubscribe'] + $arr_domino['revenueWatching'] + $arr_domino['revenueCron'];
        $sumTotal +=  $arr_onnews['revenueSubscribe'] + $arr_onnews['revenueWatching'] + $arr_onnews['revenueCron'];
        $data = array('phimonline' => $arr_phimonline, 'domino' => $arr_domino, 'onnews' => $arr_onnews, 'sum_total'=>$sumTotal);
        $data = $this->_db->valueQuote(json_encode($data));

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    vod_on_livetv_count=$data
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    vod_on_livetv_count=$data";
        $this->_db->queryWrite($sql);
    }

    public function updateSubscribe3GAndMobitv($date) {
        $toDate = $this->_db->valueQuote(date("Y-m-d", strtotime("+1 day", strtotime($date))));
        $date = $this->_db->valueQuote($date);

        $sql = "SELECT count(distinct msisdn) total FROM transaction where `action`='subscribe3G' AND content='1' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        $basic = ($row) ? $row['total'] : 0;

        $sql = "SELECT count(distinct msisdn) total FROM transaction where `action`='subscribe3G' AND content='2' AND created_time>=$date AND created_time<$toDate";
        $this->_db->query($sql);
        $row1 = $this->_db->fetchRow();
        $lite = ($row1) ? $row1['total'] : 0;

        $sql = "INSERT INTO report_daily
                SET
                    date=$date,
                    cp_id=0,
                    updated_time=now(),
                    subscribe_3g_and_basic=$basic,
                    subscribe_3g_and_lite=$lite
                ON DUPLICATE KEY UPDATE
                    updated_time=now(),
                    subscribe_3g_and_basic=$basic,
                    subscribe_3g_and_lite=$lite";
        $this->_db->queryWrite($sql);
    }

}
